package org.dbi.mysql;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;

import org.dbi.beans.*;
import org.dbi.logger.ErrorLogger;

public class MainDao {
	private DatabaseManager dbm = DatabaseManager.getInstance();
	private static MainDao dao = null;
	private ErrorLogger logger = ErrorLogger.getLogger(getClass().getName());

	// MySQL queries

	private String selectAllDatabases = "Select * from tblDatabase";
	private String selectTablesByDatabase = "Select * from tblTabel Where tblDatabase = ?";
	private String selectAttributes = "Select * from tblAttribute where tblDatabase = ? AND tblTabel = ?";
	private String selectAttributeInfoFromGroupAttrib = "Select * from tblAttribute where Naam = ? AND tblDatabase = ? AND tblTabel = ?";
	private String selectAllTables = "Select * from tblTabel";
	// private String selectGroups = "Select * from tblGroup where Naam = ?";
	private String selectReferences = "Select * from tblReference where naam = ?";

	private String selectConstraints = "Select * from tblConstraint";
	private String selectConstraintsByName = "Select * from tblConstraint where Naam = ?";
	private String selectAttribGroups = "Select * from tblAttribGroup where naam = ?";

	// MySQL PreparedStatements
	private PreparedStatement psAllDatabases = null;
	private PreparedStatement psTablesByDatabase = null;
	private PreparedStatement psGetAttributes = null;
	private PreparedStatement psGetConstraints = null;
	private PreparedStatement psGetAttribGroups = null;
	private PreparedStatement psGetReferences = null;
	private PreparedStatement psAttributeInfoFromGroupAttrib = null;
	private PreparedStatement psAllTables = null;
	private PreparedStatement psConsByName = null;

	// MySQL ResultSet
	private ResultSet rs = null;

	public static MainDao getInstance() {
		if (dao == null)
			dao = new MainDao();
		return dao;
	}

	private MainDao() {
		try {
			psAllDatabases = dbm.getConnection().prepareStatement(selectAllDatabases);
			psTablesByDatabase = dbm.getConnection().prepareStatement(selectTablesByDatabase);
			psGetAttributes = dbm.getConnection().prepareStatement(selectAttributes);
			// Groups and references
			psGetReferences = dbm.getConnection().prepareStatement(selectReferences);
			psAllTables = dbm.getConnection().prepareStatement(selectAllTables);
			// Keys
			psGetAttribGroups = dbm.getConnection().prepareStatement(selectAttribGroups);
			psGetConstraints = dbm.getConnection().prepareStatement(selectConstraints);
			psAttributeInfoFromGroupAttrib = dbm.getConnection().prepareStatement(selectAttributeInfoFromGroupAttrib);
			psConsByName = dbm.getConnection().prepareStatement(selectConstraintsByName);
			// rdbms vullen
			getDatabases();
			getTablesByDatabase();
			getAttributes();
			getConstraints();
			getReferences();
		} catch (SQLException e) {
			logger.severe(e.getMessage());
		}
	}

	public void getDatabases() {
		try {
			rs = psAllDatabases.executeQuery();
			while (rs.next()) {
				Rdbms.getInstance().createDatabase(rs.getString(1));
			}
		} catch (SQLException e) {
			logger.severe(e.getMessage());
		}
	}

	public void getTablesByDatabase() {
		for (Database db : Rdbms.getInstance().getRdbmsDatabases().values()) {
			try {
				psTablesByDatabase.setString(1, db.getNaam());
				rs = psTablesByDatabase.executeQuery();
				while (rs.next()) {
					Rdbms.getInstance().createTable(rs.getString(3), rs.getString(1), rs.getString(2));
				}
			} catch (SQLException e) {
				logger.info(e.getMessage());
			}
		}

	}

	public void getAttributes() {

		for (Database database : Rdbms.getInstance().getRdbmsDatabases().values()) {
			for (Tabel tbl : database.getTabellen().values()) {
				try {
					psGetAttributes.setString(1, database.getNaam());
					psGetAttributes.setString(2, tbl.getNaam());
					rs = psGetAttributes.executeQuery();
					while (rs.next()) {
						System.out.println(rs.getString(4));
						Rdbms.getInstance().createAttribute(rs.getString(1), rs.getString(3), rs.getString(2), rs.getString(4), rs.getBoolean(5));
					}
				} catch (SQLException e) {
					logger.severe(e.getMessage());
				}
			}
		}

	}

	public void getConstraints() {
		try {
			rs = psGetConstraints.executeQuery();
			ResultSet attribGroupsSet = null;
			while (rs.next()) {
				psGetAttribGroups.setString(1, rs.getString(3));
				attribGroupsSet = psGetAttribGroups.executeQuery();
				ArrayList<Object[]> attributen = new ArrayList<Object[]>();
				while (attribGroupsSet.next()) {
					attributen.add(getAttribute(attribGroupsSet.getString(2), attribGroupsSet.getString(4), attribGroupsSet.getString(3)));
				}

				Rdbms.getInstance().createConstraint(rs.getString(1), rs.getString(2), rs.getString(3), attributen);
			}
		} catch (SQLException e) {
			logger.severe(e.getMessage());
		}
	}

	public void getReferences() {
		try {
			ResultSet tblset = null;
			tblset = psAllTables.executeQuery();
			while (tblset.next()) {
				Rdbms.getInstance().useDatabase(tblset.getString(2));
				ResultSet myResultSet = null;
				psConsByName.setString(1, tblset.getString(3));
				rs = psConsByName.executeQuery();
				while (rs.next()) {
					if (rs.getString(2).startsWith("REF")) {
						psGetReferences.setString(1, rs.getString(2));
						myResultSet = psGetReferences.executeQuery();
						while (myResultSet.next()) {
							Rdbms.getInstance().createReference(myResultSet.getString(1), myResultSet.getString(2), myResultSet.getString(3));
						}

					}
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	private Object[] getAttribute(String naam, String database, String tabel) {
		Object[] a = null;
		ResultSet myResultSet = null;
		try {
			psAttributeInfoFromGroupAttrib.setString(1, naam);
			psAttributeInfoFromGroupAttrib.setString(2, database);
			psAttributeInfoFromGroupAttrib.setString(3, tabel);
			myResultSet = psAttributeInfoFromGroupAttrib.executeQuery();
			while (myResultSet.next()) {
				Object[] b = { myResultSet.getString(1), myResultSet.getString(4), myResultSet.getBoolean(5) };
				a = b;
				break;
			}
		} catch (Exception e) {
			logger.info(e.getMessage());
		}

		return a;
	}

}
